Querying the Data Warehouse with the SQL Procedure SELECT Statement

To appreciate the wealth of information a data warehouse has to offer, you need to understand how to communicate with it. Information is typically accessed using an SQL query. The statement responsible for querying the many tables and returning the requested results in a data warehouse environment is the SELECT statement. It is an indispensable piece of the data retrieval process. This paper looks at the structure of the typical data warehouse, the types of information stored in it, and how users access and retrieve data from it. INTRODUCTION A data warehouse plays an important role in modern computer systems. It stores and organizes operational enterprise-wide data consisting of a mix of older unintegrated "legacy" application systems. Data warehouse data is accurate as of some moment in time. Its purpose is to support management and end-user analysis and decision making. Operational data passes into a data warehouse after being captured and filtered. Only data meeting the needs of management and decision support activities is passed. As it is being passed from the operational environment, data usually undergoes a transformation process prior to being stored in the data warehouse. Structurally, data warehouse data bares no resemblance to data in the operational environment. Consequently, data redundancy is normally not a problem. THE DATA WAREHOUSE STRUCTURE A data warehouse has a unique structure as illustrated in Figure 1. It consists of current and older operational data combined with varying levels of summarizations and detailed vantage points. In Inmon and Hackathorn's book "Using the Data Warehouse", they describe data warehouse data as "data spanning a spectrum of time and the data relationships between two or more tables may be many." A data warehouse is organized with data consisting of major business areas such as customer, product, vendor, and activity. Four types of data may be stored in a data warehouse: 1) older detail data, 2) current detail data, 3) lightly summarized data, and 4) highly summarized data. Older detail data represents data that is not very recent, maybe as old as ten years or longer. It is voluminous and most frequently stored on mass storage such as tape, although more expensive disk storage may be used. Its level of detail is consistent with current detail data (see later description), but due to its longer time horizon is typically migrated to a "less-expensive" alternate storage medium. Current detail data represents data of a recent nature and always has a shorter time horizon than older detail data. Although it can be voluminous, it is almost always stored on disk to permit faster access. Lightly summarized data represents data distilled from current detail data. It is summarized according to some unit of time and always resides on disk. Highly summarized data represents data distilled from lightly summarized data. It is always compact and easily accessible and resides on disk. A final component of the data warehouse is that of metadata. Metadata is best described as data about data. Simply put, it provides information about the structure of a data warehouse as well as the various algorithms used in data summarizations. It provides a descriptive view, or "blueprint", of how data is mapped from the operational level to the data warehouse. Advanced Tutorials